#!/user/bin/evn Python3 
# -*- coding:utf-8 -*-
# project:PythonForWork
# FileToOracle.py
# Oracle数据库利文件导入导出
# author:ZhaoHeXin
# creation time:2019/12/2--14:26


from gevent import monkey, Greenlet

monkey.patch_all()
import gevent
import csv
import os
import time
import math
import queue
import threading
from core.Management import OracleManagement
from core.OsOperate import OsOperate

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))


class DataToOracle(object):
    """
    将转换好的数据格式导入到Oracle
    """

    def __init__(self, information, user, rate=5000):
        """
        接收参数
        :param information:所给数据，数据格式为字典类型，key为table_name,data
                data中多行数据用元祖表示
        :user: 连接数据库用户名 格式为"*/*@*.*.*.*:*/*"
        :rate: 表示多少数据一插入 默认为50000
        """
        self.information = information
        self.user = user
        self.rate = rate

    def __call__(self, *args, **kwargs):
        """
        利用所传数据列向Oracle数据库特定表导入数据
        :return:结果值及时间
        """
        # 整理数据
        assembly = self.data_assembly()
        sql = assembly["sql"]
        data = assembly["data"]
        table_name = assembly["table_name"]
        title = assembly["title"]
        # 连接数据库
        with OracleManagement(self.user) as cursor:
            # 检查sql
            self.sql_checkpoint(sql, cursor[1])
            # 导入sql
            self.sql_input(data, cursor[1], table_name, title, cursor[0])

    def data_assembly(self):
        """
        数据重组，将所读取数据重组为所需要的一系列数据
        :data:传入文件，传入文件必须问字典类型，key为table_name,data
        :return:返回字典，key为sql,data,table_neme,title 后两个用于报错信息
        """
        table_name = self.information["table_name"]
        data = self.information["data"]
        title = ",".join([str(i) for i in data[0]])
        value = ",".join([f":{i + 1}" for i in range(0, len(data[0]))])
        # 组装sql语句
        sql = f"INSERT INTO {table_name} ({title}) VALUES ({value})"
        # 去除数据标题
        data.remove(data[0])
        return {"sql": sql, "data": data, "table_name": table_name, "title": title}

    def sql_checkpoint(self, sql, cursor):
        """
        sql数据检查
        :param sql:需要检查的sql
        :param cursor:数据库游标
        :return:返回结果值
        """
        # 检查并定义sql
        print(f"===prepare begin==={time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))}===")
        cursor.prepare(sql)
        print(f"===prepare end==={time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))}===")
        return True

    def sql_input(self, data, cursor, table_name, title, con):
        """
        sql具体导入
        :param data:需要导入的数据，list类型，每行用元祖表示
        :param cursor:数据库游标
        :return:返回结果值
        @param con: 数据库连接
        """
        range_ = math.ceil(len(data) / self.rate) + 1
        for i in range(1, range_):
            begin = (i - 1) * self.rate
            end = i * self.rate
            # 开启协程入库
            oracle_list = [gevent.spawn(self.oracle_input, begin, end, data, cursor, table_name, title, con)]
        for k in oracle_list:
            k.join()
        print('execute end')

    def oracle_input(self, begin, end, data, cursor, table_name, title, con):
        try:
            time.sleep(0.01)
            print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())), '=>',
                  threading.currentThread().ident, "开始数据导入")
            cursor.executemany(None, data[begin:end])
            con.commit()
            print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())), '=>', begin, '-', end, '(',
                  len(data[begin:end]), ')', 'finish')

        except Exception as e:
            OsOperate(os.path.join(BASE_DIR, f"错误数据")).creat_dir()
            with open(os.path.join(BASE_DIR, f"错误数据\\{table_name}.txt"), "a+", encoding="utf-8") as f:
                f.write(f"{title}\n")
                for k in data[begin:end]:
                    f.write(f"{','.join(str(j) for j in k)}\n")
            with open(os.path.join(BASE_DIR, f"错误数据\\log.txt"), "a+", encoding="utf-8") as f:
                f.write(
                    f"{str(e)}, 报错区间为{table_name}===>{begin}-->{end}\n")
            print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())), '=>', begin, '-', end, '(',
                  len(data[begin:end]), ')', 'filed')


class CsvDataConversion(object):
    """
    将csv中的数据进行格式转换成进入Oracle所需要的数据格式
    """

    def __init__(self, file_name, change=None, rate=10000):
        """
        接收参数
        @param file_name: 需要转换文件的文件绝对路径
        @param change:是否需要数据转换，即某些数据是否需要换成其他数据,
        格式为字典【"替换文件路径":{"需要替换表":[替换文件列：需要替换列：位数]}】
        """
        self.file_name = file_name
        self.change = change
        self.q = queue.Queue(maxsize=0)
        self.rate = rate

    def __call__(self, *args, **kwargs):
        """
        执行函数
        @return:返回所需的数据列表
        """
        # 获取数据
        data = self.data_read(self.file_name)
        # 数据整理
        self.data_arrange(data)
        # 获取所有整理后的数据
        print("--------开始整合数据，请稍后-----------")
        conversion_datas = []
        while True:
            try:
                conversion_data = self.q.get()
                conversion_datas.append(conversion_data)
            except Exception as e:
                break
        table_name = self.file_name.split("\\")[-1].split(".")[0]
        return {"table_name": table_name, "data": conversion_datas}

    def gain_file(self):
        """
            获取文件列表，只遍历所给目录中的文件，若存在目录，则跳过，若是非csv文件，跳过
            @return: 文件列表
            """
        if os.path.isfile(self.path):
            return [self.path]
        else:
            file_list = []
            list_ = [os.path.join(self.path, p) for p in OsOperate(self.path).list_dir() if
                     p.split('.')[-1] == "csv"]
            for file in list_:
                if os.path.isfile(file):
                    file_list.append(file)
            return file_list

    def data_read(self, file_name):
        """
        读取文件数据并进行整理数据
        @param file_name:需要读取文件的路径(csv)
        @return:文件读取的内容结果
        """
        data = []
        with open(file_name, "r", encoding='utf8') as f:
            reader = csv.reader(f)
            for line in reader:
                data.append(tuple(line))
        return data

    def data_arrange(self, data):
        """
        将数据整理成所需要的格式
        @param data:传入基本数据
        @return:返回整理好的数据
        """
        table_name = self.file_name.split('\\')[-1].split('.')[0]
        if self.change is not None:
            for r_path, r_rules in self.change.items():
                # 获取需要替换的具体规则
                for r_rule in r_rules:
                    # 判断是否为需要替换数据的表
                    if table_name == r_rule[0]:
                        # 进行数据整理
                        data = self.data_replace_gre(data, r_path, r_rule)
                        break
        return {"table_name": table_name, "data": data}

    def data_replace_gre(self, data, r_path, r_rule):
        """
        替换文件内容
        创建协成操作
        @param r_path:替换数据所在源文件地址
        @param r_rule:替换规则
        @param data:需要被替换的内容
        {"替换文件路径":[{"需要替换表":[参考文件中的列名：需要替换数据表的列名：位数]]}
        @return:替换好的内容
        """
        # 读取文件内容
        r_data = []
        with open(r_path, "r", encoding='utf8') as f:
            reader = csv.reader(f)
            for line in reader:
                r_data.append(tuple(line))
        # 确定修改规则
        # 需要修改的字段集合
        r_rule.remove(r_rule[0])
        # 获取需要修改数据字段的下标
        primary = [j for j, k in enumerate(data[0]) if k in [i.split(":")[1] for i in [k for k in r_rule]]]
        # 获取参考数据字段下标[列表推导式会自动去重数字]
        r_primary = []
        r_primary_name = [i.split(":")[0] for i in [k for k in r_rule]]
        for i in r_primary_name:
            for j, k in enumerate(r_data[0]):
                if k in i:
                    r_primary.append(j)
        # 获取截位规则
        r_cut = [i.split(":")[2] for i in [k for k in r_rule]]
        # 获取开启协成数量并去除标题
        range_ = math.ceil(len(r_data) / self.rate) + 1
        # 开始进行协成创建并修改数据
        g_list = []
        # 去除标题
        for i in range(1, range_):
            begin = (i - 1) * self.rate
            end = i * self.rate
            g_list.append(gevent.spawn(self.data_replace, data, r_data[begin:end], primary, r_primary, r_cut))
        for k in g_list:
            k.join()
        print('consolidation  end')
        return True

    def data_replace(self, datas, r_datas, primarys, r_primarys, r_cuts):
        """
        单次批量替换数据，协成数量进行快速修改数据
        @param r_cuts: 截位规则，ALL代表不进行截位
        @param primarys:需要修改数据的下标
        @param r_primarys:参考文件中字段的下标，上述一一对应
        @param datas: 需要修改的数据
        @param r_datas: 参照文件
        @return:
        """
        # 存放标题后删除
        print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())), '=>', threading.currentThread().ident,
              "开始组装数据")
        self.q.put(datas[0])
        r_datas.remove(r_datas[0])
        datas.remove(datas[0])
        for r_data in r_datas:
            r_data = list(r_data)
            time.sleep(0.001)
            for data in datas:
                data = list(data)
                for i in range(0, len(primarys)):
                    if r_cuts[i] == "ALL":
                        data[primarys[i]] = data[primarys[i]].replace(data[primarys[i]], r_data[r_primarys[i]])
                    else:
                        cut = r_cuts[i].split("-")[0]
                        r_cut = r_cuts[i].split("-")[1]
                        data[primarys[i]] = data[primarys[i]].replace(data[primarys[i]][int(cut):int(r_cut)],
                                                                      r_data[r_primarys[i]][int(cut):int(r_cut)])
                self.q.put(tuple(data))
            print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())), '=>',
                  threading.currentThread().ident,
                  f"完成数据组装-{r_data[0]}")


if __name__ == '__main__':
    path = r"C:\Users\ZhaoHeXin\Desktop\1111\LABOUR_SURVEY_TENEMENT_LSB.csv"
    # path = r"C:\Users\ZhaoHeXin\Desktop\1111"
    data_conversion = CsvDataConversion(path, {
        r"C:\Users\ZhaoHeXin\Desktop\1111\SJMB.csv": [
            ["LABOUR_SURVEY_TENEMENT_LSB", "DCQBM:AREACODE:0-12", "DCQBM:TENEMENTGROUPCODE:0-12",
             "DCQBM:TENEMENT:0-12",
             "CUN:DOORPLATE:ALL"],
            ["LABOUR_SURVEY_TENEMENT_LSB", "zd2:zd2:22"]]})
    conversion = data_conversion()
    oracle_input = DataToOracle(conversion, "cjcl/cjclpassword@192.168.1.219:1521/ytb_gj")
    oracle_input()
